---明细
insert overwrite table jms_dm.dm_oms_order_mark_count_detail_hi partition(dt)
select
    t1.order_id   --订单号
     ,t1.waybill_id --运单号
     ,t1.field_name --打标字段
     ,t2.order_source_code --订单来源code
     ,t2.order_source_name --订单来源name
     ,t2.input_time as order_input_time --订单录入时间
     ,t2.order_status_code --订单状态code
     ,case when  t2.order_status_code='100' then '未调派'
           when  t2.order_status_code='106' then '己调派代理区'
           when  t2.order_status_code='101' then '已调派网点'
           when  t2.order_status_code='102' then '已调派业务员'
           when  t2.order_status_code='103' then '已取件'
           when  t2.order_status_code='104' then '已取消'
           when  t2.order_status_code='105' then '取件失败'
    end as order_status_name      --订单状态
     ,t2.pick_network_code          --取件网点code
     ,net.name as pick_network_name --取件网点名称
     ,t2.sender_province_id
     ,t2.sender_province_name
     ,t2.sender_city_id
     ,t2.sender_city_name
     ,t2.sender_area_id
     ,t2.sender_area_name
     ,t2.receiver_province_id
     ,t2.receiver_province_name
     ,t2.receiver_city_id
     ,t2.receiver_city_name
     ,t2.receiver_area_id
     ,t2.receiver_area_name
     ,t1.input_time as mark_input_time
     ,t2.customer_code
     ,t2.customer_name
     ,net.agent_code pick_agent_code
     ,net.agent_name pick_agent_name
     ,net.virt_code  pick_virt_code
     ,net.virt_name  pick_virt_name
     ,concat(date_format(t1.input_time,'yyyy-MM-dd HH:'),'00:00') as date_time
     ,dict.mark_name as field_value --打标值
     ,t1.dt as dt
from
    jms_dwd.dwd_yl_oms_order_mark_expand_base_hi t1
    inner join jms_dim.dim_yl_oms_order_mark_dict_base_hi dict on dict.sync_big_data=1 and dict.is_delete = 0 and dict.mark_key=t1.field_name
    left join
    (
        select  id,waybill_id,
                order_source_code,
                order_source_name,
                input_time,
                order_status_code, --
                pick_network_code, ---
                sender_province_id,
                sender_province_name,
                sender_city_id,
                sender_city_name,
                sender_area_id,
                sender_area_name,
                receiver_province_id,
                receiver_province_name,
                receiver_city_id,
                receiver_city_name,
                receiver_area_id,
                receiver_area_name,
                customer_code,
                customer_name,
                row_number() over(partition by id order by dt desc) as rk
        from jms_dwd.dwd_yl_oms_oms_order_hf --小时订单表更新最近15天的数据
        where SUBSTR(dt,1,10) between date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
    )t2 on t2.id=t1.order_id and t2.rk=1
    left join jms_dim.dim_network_whole_massage net on net.code= t2.pick_network_code
    where t1.dt between '{{ execution_date | cst_hour }}'  and '{{ execution_date | cst_hour }}'
    distribute by dt,abs(hash(order_id))%20;
------汇总
insert overwrite table jms_dm.dm_oms_order_mark_count_hi partition(dt)
select
    pick_agent_code     --取件代理区code
     ,pick_agent_name    --取件代理区name
     ,field_name         --打标名称
     ,order_status_code  --订单状态code
     ,order_status_name  --订单状态name
     ,date_time          --打标时间小时
     ,count(1) as mark_order_cnt          --打标订单量
     ,field_value
     ,dt                 --分区
from jms_dm.dm_oms_order_mark_count_detail_hi
where dt between '{{ execution_date | cst_hour }}' and '{{ execution_date | cst_hour }}'
group by order_status_code
       ,order_status_name
       ,pick_agent_code
       ,pick_agent_name
       ,field_name
       ,date_time
       ,dt
       ,field_value
distribute by dt;